﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public class ConnectionToDataBase
    {
        int check;
        static SqlConnection _SqlConnection;      
        
      
        public static SqlConnection StartConnection()
        {
            try
            {
                string strConnection = "Server=.;DataBase=TarsDeliverySystemDemo; Integrated Security=yes";
                _SqlConnection = new SqlConnection(strConnection);
                return _SqlConnection;
            }
            catch (Exception _epx)
            {
                throw _epx;
            }
        }

        public static SqlConnection CloseConnection()
        {
            _SqlConnection.Close();
            return _SqlConnection;
        }
        public DataTable executeSelectQueryNonParameter(string _query)
        {

            SqlCommand selectCommand = new SqlCommand();
            DataSet ds = new DataSet();
            try
            {
                selectCommand.Connection = StartConnection();
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.CommandText = _query;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                da.Fill(ds);

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
            finally
            {
                CloseConnection();
            }
            return ds.Tables[0];
        }
        public DataTable executeSelectQuery(string _query, SqlParameter[] sqlParameters)
        {
            SqlCommand selectCommand = new SqlCommand();

            DataSet ds = new DataSet();
            try
            {
                selectCommand.Connection = StartConnection();
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.CommandText = _query;
                selectCommand.Parameters.AddRange(sqlParameters);
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);

                da.Fill(ds);
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
            finally
            {
                CloseConnection();
            }
            return ds.Tables[0];
        }
        public bool executeInserQuery(string _query, SqlParameter[] sqlParameters)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand insertCommand = new SqlCommand();
            try
            {
                insertCommand.Connection = StartConnection();
                _SqlConnection.Open();
                insertCommand.CommandType = CommandType.StoredProcedure;
                insertCommand.CommandText = _query;
                insertCommand.Parameters.AddRange(sqlParameters);
                insertCommand.ExecuteNonQuery();
                da.InsertCommand = insertCommand;
                return true;
            }
            catch (SqlException ex)
            {
            Console.WriteLine(ex.Message);
            Console.ReadLine();
                return false;
            }
            finally
            {
                _SqlConnection.Close();
            }
            
        }
        public bool executeUpdateQuery(string _query, SqlParameter[] sqlParameters)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand updateCommand = new SqlCommand();
            try
            {
                updateCommand.Connection = StartConnection();
                _SqlConnection.Open();
                updateCommand.CommandType = CommandType.StoredProcedure;
                updateCommand.CommandText = _query;
                updateCommand.Parameters.AddRange(sqlParameters);
                updateCommand.ExecuteNonQuery();
                da.UpdateCommand = updateCommand;
                return true;
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
                return false;
            }
            finally
            {
                _SqlConnection.Close();
            }
           
        }
        public bool executeDeleteQuery(string _query, SqlParameter[] sqlParameters)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand deleteCommand = new SqlCommand();
            try
            {
                deleteCommand.Connection = StartConnection();
                _SqlConnection.Open();
                deleteCommand.CommandType = CommandType.StoredProcedure;
                deleteCommand.CommandText = _query;
                deleteCommand.Parameters.AddRange(sqlParameters);
                deleteCommand.ExecuteNonQuery();
                da.DeleteCommand = deleteCommand;
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
                return false;
            }
            finally
            {
                CloseConnection();
            }
            return true;
        }
        public int executeExecuteScalar(string _query, SqlParameter[] sqlParameters)
        {

            SqlCommand selectCommand = new SqlCommand();
            try
            {
                selectCommand.Connection = StartConnection();
                _SqlConnection.Open();
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.CommandText = _query;
                selectCommand.Parameters.AddRange(sqlParameters);

                //SqlDataAdapter da = new SqlDataAdapter(selectCommand);

                check = Convert.ToInt32(selectCommand.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
            finally
            {
                _SqlConnection.Close();
            }
            return check;
        }
        public int executeExecuteScalarNonParameter(string _query)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand selectCommand = new SqlCommand();

            try
            {
                selectCommand.Connection = StartConnection();
                _SqlConnection.Open();
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.CommandText = _query;
                da.SelectCommand = selectCommand;
                check = Convert.ToInt32(selectCommand.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
            finally
            {
                _SqlConnection.Close();
            }
            return check;
        }
    }
}
